#include "session.h"
#include "Manager.h"
#include "Employee.h"
#include "SQLAPI.h"
#include <stdio.h>
#include <vector>
#include <string>
#include <iostream>

using namespace std;


 Session::Session()
{
	SAConnection con;
	try{

		//connect to db 
		con.Connect("db","abc","pass", SA_MySQL_Client);

		//set up manager and employee tables making sure userid and name are unique fields
		SACommand cmd;
		cmd.setConnection(&con);
		cmd.setCommandText("Create table employees(name varchar(30), password varchar(15), availability varchar(15))"); //create employees table
		cmd.Execute();
		cmd.setCommandText("create unique index idx on employees (name)");
		cmd.Execute();
		cmd.setCommandText("Create table managers(name varchar(30), password varchar(15))");
		cmd.Execute();
		cmd.setCommandText("create unique index idx on managers (name)");
		cmd.Execute();

		//hard coded admin manager with userid 1
		cmd.setCommandText("Insert into managers(name, password) values('admin', 'adminpass')");
		cmd.Execute();

		con.Disconnect();
	}
	//error checking
	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}

void Session::addEmployee( Employee employee )
{
	SAConnection con;
	try{
		con.Connect("db","abc","pass", SA_MySQL_Client);

		string SQLquery = "insert into employees(name, password, availability) values('" + employee.getName() + "','"+employee.getPassword()+"','"+employee.getAvailability()+"')";
		SACommand cmd;
		cmd.setConnection(&con);
		SAString sastr(SQLquery.c_str());
		cmd.setCommandText(sastr);
		cmd.Execute();

		con.Disconnect();
	}

	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}

void Session::addManager( Manager manager)
{
	SAConnection con;
	try{
		con.Connect("db","abc","pass", SA_MySQL_Client);
		
		string SQLquery = "insert into managers(name, password) values('" + manager.getName() + "','"+manager.getPassword()+"')";
		SACommand cmd;
		cmd.setConnection(&con);
		SAString sastr(SQLquery.c_str());
		cmd.setCommandText(sastr);
		cmd.Execute();

		con.Disconnect();
	}
	
	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}

void Session::deleteEmployee( string name )
{
	SAConnection con;
	try{
		con.Connect("db","abc","pass", SA_MySQL_Client);

		SACommand cmd;
		cmd.setConnection(&con);
		
		string SQLquery = "delete from employees where name='"+name+"'";
		SAString sastr( SQLquery.c_str() );
		cmd.setCommandText(sastr);
		cmd.Execute();

		con.Disconnect();
	}

	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}


void Session::deleteManager( string name )
{
	SAConnection con;

	try{
		if( name != "admin" ){
			con.Connect("db","abc","pass", SA_MySQL_Client);

			SACommand cmd;
			cmd.setConnection(&con);

			string SQLquery = "delete from managers where name='"+name+"'";
			SAString sastr( SQLquery.c_str() );
			cmd.setCommandText(sastr);
			cmd.Execute();

			con.Disconnect();
		}
		else{
			cout << "Can not delete admin account" << endl;
		}
		
	}

	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}

void Session::changeEmployeePassword( string name, string newpassword )
{
	SAConnection con;
	try{
		con.Connect("db","abc","pass", SA_MySQL_Client);

		SACommand cmd;
		cmd.setConnection(&con);

		string SQLquery = "update employees set password='"+newpassword+"' where name='"+name+"'";
		SAString sastr( SQLquery.c_str() );
		cmd.setCommandText( sastr );
		cmd.Execute();

		con.Disconnect();
	}
	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}

void Session::changeManagerPassword( string name, string newpassword)
{
	SAConnection con;
	try{
		con.Connect("db","abc","pass", SA_MySQL_Client);

		SACommand cmd;
		cmd.setConnection(&con);
		if( name != "admin" ){
			string SQLquery = "update managers set password='"+newpassword+"' where name='"+name+"'";
			SAString sastr( SQLquery.c_str() );
			cmd.setCommandText( sastr );
			cmd.Execute();
		}
		else{
			cout << "Can not change admin password" << endl;
		}
		con.Disconnect();
	}
	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}


string Session::getEmployeePassword( string name )
//RETURNS "" ON FAILURE
{
	SAConnection con;
	try{
		
		con.Connect("db","abc","pass", SA_MySQL_Client);
		
		SACommand cmd;
		cmd.setConnection(&con);
		string SQLquery = "select password from employees where name='"+name+"'";
		SAString sastr( SQLquery.c_str() );
		cmd.setCommandText( sastr );
		cmd.Execute();

		cmd.FetchNext();
		string myName = cmd[1].asString();

		con.Disconnect();

		return myName;
		
	}
	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}

string Session::getManagerPassword( string name )
//RETURNS "" ON FAILURE
{
	SAConnection con;
	try{
		
		con.Connect("db","abc","pass", SA_MySQL_Client);
		
		SACommand cmd;
		cmd.setConnection(&con);
		string SQLquery = "select password from managers where name='"+name+"'";
		SAString sastr( SQLquery.c_str() );
		cmd.setCommandText( sastr );
		cmd.Execute();

		cmd.FetchNext();
		string myName = cmd[1].asString();

		con.Disconnect();

		return myName;
		
	}
	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}


void Session::displayAllUsers()
{
	SAConnection con;
	try{
		
		con.Connect("db","abc","pass", SA_MySQL_Client);
		
		SACommand cmd;
		cmd.setConnection(&con);
		cmd.setCommandText("select name, password from managers");
		cmd.Execute();
		cout << "Managers" << endl;

		while( cmd.FetchNext() ){
			string s = cmd[1].asString();
			string s2 = cmd[2].asString();
			if( s != "admin" ){
				cout << s << " " << s2 << endl;
			}
		}

		cmd.setCommandText("select name, password from employees");
		cmd.Execute();
		cout << endl << "Employees" << endl;
		while(cmd.FetchNext() ){
            string s = cmd[1].asString();	
			string s2 = cmd[2].asString();
			cout << s << " " << s2 << endl;
		}	
		con.Disconnect();
	}
	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}

void Session::setAvailability( string name, string availability ) 
{
	const int AVAILABILITY_MAX_SIZE = 14;
	if ( availability.size() == AVAILABILITY_MAX_SIZE ){
		SAConnection con;
		try{
			
			con.Connect("db","abc","pass", SA_MySQL_Client);
			
			SACommand cmd;
			cmd.setConnection(&con);
		
			string SQLquery = "update employees set availability='"+availability+"' where name='"+name+"'";
			SAString sastr( SQLquery.c_str() );
			cmd.setCommandText( sastr );
			cmd.Execute();		

			con.Disconnect();
			
		}
		catch( SAException &x)
		{
			try{
				con.Rollback();
			}
			catch(SAException &)
			{
			}
			printf("%s\n", (const char*)x.ErrText());
		}
	}
	else{
		cout << "Availability string is not 14 digits" << endl;
	}
}

string Session::getAvailability( string name )
{

	SAConnection con;
	try{
		
		con.Connect("db","abc","pass", SA_MySQL_Client);
		
		SACommand cmd;
		cmd.setConnection(&con);
		
		string SQLquery = "select availability from employees where name ='"+name+"'";
        SAString sastr( SQLquery.c_str() );
		cmd.setCommandText( sastr );
		cmd.Execute();	

		
		cmd.FetchNext();
		string myName = cmd[1].asString();

		con.Disconnect();

		return myName;
		
	}
	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}

vector<Employee> Session::getAllEmployees()
{
	SAConnection con;
	try{
		
		con.Connect("db","abc","pass", SA_MySQL_Client);
		
		SACommand cmd;
		cmd.setConnection(&con);
		
		string SQLquery = "select * from employees";
        SAString sastr( SQLquery.c_str() );
		cmd.setCommandText( sastr );
		cmd.Execute();	

		vector<Employee> employees;
		while ( cmd.FetchNext() ){
			string myName = cmd[1].asString();
			string myPass = cmd[2].asString();
			string myAvail = cmd[3].asString();
			Employee emp( myName, myPass, myAvail);
			employees.push_back( emp );
		}
		

	return employees;
		
	}
	catch( SAException &x)
	{
		try{
			con.Rollback();
		}
		catch(SAException &)
		{
		}
		printf("%s\n", (const char*)x.ErrText());
	}
}


